﻿Imports System.Data.OleDb

Public Class ProductGateway
    Inherits DataGateway

    Protected Overrides ReadOnly Property FindAllQuery As String
        Get
            Return "SELECT * FROM ProductListView"
        End Get
    End Property

    Protected Overrides ReadOnly Property DeleteQuery As String
        Get
            Return "DELETE FROM Product"
        End Get
    End Property

    Protected Overrides ReadOnly Property SiteUserQuery() As String
        Get
            Return "SELECT SiteUser.* " &
                   "FROM Product LEFT JOIN SiteUser " &
                   "ON Product.UserID=SiteUser.ID " &
                   "WHERE Product.ID=@ID"
        End Get
    End Property

    Public Function Insert(ByVal model As String, ByVal manufacturer As String, ByVal url As String,
                           ByVal length As UInteger, ByVal height As UInteger, ByVal width As UInteger,
                           ByVal userID As ULong) As ULong
        Dim newID As ULong
        Dim sqlQuery As String = "INSERT INTO Product (Model, Manufacturer, URL, Length, Height, Width, UserID) VALUES (@Model, @Manufacturer, @URL, @Length, @Height, @Width, @UserID)"
        Using connection As New OleDbConnection(ConnectionString)
            Using command As New OleDbCommand(sqlQuery, connection)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@Model", model)
                command.Parameters.AddWithValue("@Manufacturer", manufacturer)
                command.Parameters.AddWithValue("@URL", url)
                command.Parameters.AddWithValue("@Length", length)
                command.Parameters.AddWithValue("@Height", height)
                command.Parameters.AddWithValue("@Width", width)
                command.Parameters.AddWithValue("@UserID", userID)
                connection.Open()
                command.ExecuteNonQuery()
                newID = GetLastInsertID(command)
            End Using
        End Using

        Return newID
    End Function

    Public Sub Update(ByVal productID As ULong, ByVal model As String, ByVal manufacturer As String, ByVal url As String,
                      ByVal length As UInteger, ByVal height As UInteger, ByVal width As UInteger)
        Dim sqlQuery As String = "UPDATE Product SET Model=@Model, Manufacturer=@Manufacturer, URL=@URL, Length=@Length, Height=@Height, Width=@Width WHERE ID=@ProductID"
        Using connection As New OleDbConnection(ConnectionString)
            Using command As New OleDbCommand(sqlQuery, connection)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@Model", model)
                command.Parameters.AddWithValue("@Manufacturer", manufacturer)
                command.Parameters.AddWithValue("@URL", url)
                command.Parameters.AddWithValue("@Length", length)
                command.Parameters.AddWithValue("@Height", height)
                command.Parameters.AddWithValue("@Width", width)
                command.Parameters.AddWithValue("@ProductID", productID)
                connection.Open()
                command.ExecuteNonQuery()
            End Using
        End Using
    End Sub
End Class
